import pymysql
import os

# 读取数据库配置
with open(r's:\diet_system\backend\config\db_config.py', 'r', encoding='utf-8') as f:
    config_content = f.read()
    
# 解析配置信息
exec(config_content)

# 读取SQL脚本
with open(r's:\diet_system\backend\database_setup.sql', 'r', encoding='utf-8') as f:
    sql_script = f.read()

# 分割SQL语句
sql_statements = sql_script.split(';')

# 连接到MySQL服务器（不指定数据库）
try:
    print("正在连接到MySQL服务器...")
    conn = pymysql.connect(
        host=DB_CONFIG['host'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        port=DB_CONFIG['port']
    )
    cursor = conn.cursor()
    print("成功连接到MySQL服务器！")
    
    # 执行SQL语句
    print("正在执行SQL脚本初始化数据库...")
    for statement in sql_statements:
        statement = statement.strip()
        if statement and not statement.startswith('--'):
            try:
                cursor.execute(statement)
                # 如果是CREATE DATABASE语句，需要特殊处理
                if statement.lower().startswith('create database'):
                    db_name = DB_CONFIG['database']
                    print(f"创建数据库 {db_name} 成功！")
                    conn.select_db(db_name)
                # 如果是USE语句，选择数据库
                elif statement.lower().startswith('use '):
                    db_name = statement.split()[1]
                    conn.select_db(db_name)
                    print(f"正在使用数据库 {db_name}")
            except Exception as e:
                # 忽略重复创建数据库或表的错误
                if '1007' not in str(e) and '1050' not in str(e):
                    print(f"执行SQL语句时出错: {statement}")
                    print(f"错误信息: {e}")
    
    conn.commit()
    print("数据库初始化成功！")
except Exception as e:
    print(f"数据库初始化失败: {e}")
    import traceback
    traceback.print_exc()
finally:
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals():
        conn.close()

# 测试用户数据
print("\n测试数据库中的用户数据:")
try:
    conn = pymysql.connect(
        host=DB_CONFIG['host'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        database=DB_CONFIG['database'],
        port=DB_CONFIG['port']
    )
    cursor = conn.cursor()
    cursor.execute("SELECT username, password, role FROM user")
    users = cursor.fetchall()
    if users:
        print(f"找到 {len(users)} 个用户:")
        for user in users:
            print(f"用户名: {user[0]}, 密码: {user[1]}, 角色: {user[2]}")
    else:
        print("未找到用户数据")
finally:
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals():
        conn.close()

print("\n数据库初始化脚本执行完毕。")